package org.deschutter.dump;

import org.deschutter.Constants;
import org.deschutter.URLReader;
import org.deschutter.Queries;
import org.deschutter.exceptions.CurrenttickNotFoundException;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;

import java.net.MalformedURLException;
import java.sql.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.math.BigDecimal;

/**
 * User: Berten De Schutter
 * Date: 06-Jul-2009
 * Time: 13:29:07
 * <p/>
 * This Class Handels the planet dump
 */
public class GalaxyDump implements Constants, Job {

    public static void loadFileinDB(String file, Connection conn) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement();
        st.executeUpdate("DELETE FROM galaxies_add");

        rs = st.executeQuery("LOAD DATA LOCAL INFILE '" + file + "' into table galaxies_add fields optionally enclosed by '\"' ignore 7 lines");
        rs.close();
        st.close();

    }

    public static void calculateScoreRank(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();

        st.executeUpdate("DELETE FROM galaxy_score_rank_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO galaxy_score_rank_temp"
                + " SELECT @rank:=@rank+1 AS rank,x,y"
                + " FROM galaxies_add"
                + " order by `score` desc;");

        st.close();
    }

    public static void calculateValueRank(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();

        st.executeUpdate("DELETE FROM galaxy_value_rank_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO galaxy_value_rank_temp"
                + " SELECT @rank:=@rank+1 AS rank,x,y"
                + " FROM galaxies_add"
                + " order by `value` desc;");

        st.close();

    }

    public static void calculateSizeRank(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();

        st.executeUpdate("DELETE FROM galaxy_size_rank_temp");
        st.executeQuery("SET @rank=0;");
        int a = st.executeUpdate("INSERT INTO galaxy_size_rank_temp"
                + " SELECT @rank:=@rank+1 AS rank,x,y"
                + " FROM galaxies_add"
                + " order by `size` desc;");

        st.close();

    }

    public static void populateHistory(Connection conn, String tick) throws SQLException {

        Statement st = null;


        String query = "INSERT INTO galaxies_history"
                + " (SELECT "
                + "g.x,"
                + "g.y,"
                + "g.galaxy_name,"
                + "g.size,"
                + "g.score"
                + ",g.value,"
                + "g.xp,"
                + tick + " - 1,"
                + "sr.ID,"
                + "sir.ID,"
                + "vr.ID "
                + "FROM galaxies g "
                + "INNER JOIN galaxy_score_rank sr ON (sr.x=g.x and sr.y=g.y) "
                + "INNER JOIN galaxy_size_rank sir ON (sir.x=g.x and sir.y = g.y) "
                + "INNER JOIN galaxy_value_rank vr on (vr.x=g.x and vr.y=g.y))";

        st = conn.createStatement();

        int a = st.executeUpdate(query);

        st.close();


    }

    public static void populateCurrentTables(Connection conn) throws SQLException {
        Statement st = null;


        st = conn.createStatement();
        st.executeUpdate("DELETE FROM galaxies");
        st.executeUpdate("INSERT INTO galaxies SELECT * from galaxies_add");

        st.executeUpdate("DELETE FROM galaxy_score_rank");
        st.executeUpdate("INSERT INTO galaxy_score_rank SELECT * from galaxy_score_rank_temp");

        st.executeUpdate("DELETE FROM galaxy_size_rank");
        st.executeUpdate("INSERT INTO galaxy_size_rank SELECT * from galaxy_size_rank_temp");

        st.executeUpdate("DELETE FROM galaxy_value_rank");
        st.executeUpdate("INSERT INTO galaxy_value_rank SELECT * from galaxy_value_rank_temp");
        st.close();

    }

    public static void runTickDump(String galaxy_listing_url) {
        double time = System.currentTimeMillis();
        Connection conn = null;
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();

            conn = DriverManager.getConnection("jdbc:mysql://" + MYSQL_HOST + "/" + MYSQL_TABLE + "?"
                    + "user=" + MYSQL_USER + "&password=" + MYSQL_PASSWORD);

            // Step 1:  Get planet_listing
            URLReader.copy(galaxy_listing_url, LOCAL_GALAXY_LISTING);

            // Step 2: Get current Tick ( from planet dump)
            String dump = URLReader.get(galaxy_listing_url);
            Pattern compiledRegex;
            Matcher regexMatcher;
            compiledRegex = Pattern.compile("Tick: (\\d+)\\s*");
            regexMatcher = compiledRegex.matcher(dump.replace(",", ""));
            regexMatcher.find();
            String tick = regexMatcher.group(1);

            // Step 3: Load planet listing into table
            GalaxyDump.loadFileinDB(LOCAL_GALAXY_LISTING, conn);

            // Step 4: Calulate ranks
            GalaxyDump.calculateScoreRank(conn);
            GalaxyDump.calculateValueRank(conn);
            GalaxyDump.calculateSizeRank(conn);

            // Step 5: Poluate history tables
            GalaxyDump.populateHistory(conn, tick);

            // Step 6: Populate planets tables from temp tables
            GalaxyDump.populateCurrentTables(conn);
            time = System.currentTimeMillis() - time;
            System.out.println("Tick " + tick + ": Galaxy Listing Successfully dumped in " + time / 1000 + " seconds");

        } catch (SQLException ex) {
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (MalformedURLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        runTickDump(GALAXY_LISTING_URL);
    }
}
